package com.example.service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.transaction.Transactional;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;

import com.neo.JpaThymeleafApplication;
import com.neo.entity.Order;
import com.neo.util.ReadFromFile;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = JpaThymeleafApplication.class)
public class JdbcTemplateBatchUpdate {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	// jdbc 批量更新
	@Test
	public void batchUpdate() {
		List<Order> orders = getSourceData();
		long start = System.currentTimeMillis();
		updateOrder(orders);
		System.out.println("============" + (System.currentTimeMillis() - start) / 1000);
		updateUser(orders);
		System.out.println("============ok===" + (System.currentTimeMillis() - start) / 1000);
	}

	private void updateUser(List<Order> orders) {
		String updateSql2 = "UPDATE user_base a SET  a.account_id=?  WHERE user_id = (select user_id from order_base where loan_id =? limit 1) ";
		jdbcTemplate.batchUpdate(updateSql2, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement preStatement, int i) throws SQLException {
				preStatement.setString(1, orders.get(i).getUsername());
				preStatement.setString(2, orders.get(i).getLoanid());
			}
			@Override
			public int getBatchSize() {
				return orders.size();//这个必须是list.size 不能改
			}
		});
	}

	private void updateOrder(List<Order> orders) {
		String updateSql = "UPDATE order_base a SET  a.account_id=?  WHERE loan_id =? ";

		jdbcTemplate.batchUpdate(updateSql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement preStatement, int i) throws SQLException {
				preStatement.setString(1, orders.get(i).getUsername());
				preStatement.setString(2, orders.get(i).getLoanid());
			}
			@Override
			public int getBatchSize() {
				return orders.size();
			}
		});
	}

	private List<Order> getSourceData() {
		List listCsv = ReadFromFile.readFileByLines("D:/temp/名单.csv");
		List<Order> orders = new ArrayList<>();
		for (int i = 1; i < listCsv.size(); i++) {// test
			String[] s = (String[]) listCsv.get(i);
			Order o = new Order();
			o.setLoanid(s[0]);
			o.setUsername(s[2]);
			orders.add(o);
		}
		System.out.println("list size ============" + (orders.size()));
		return orders;
	}

	@Test
	@Transactional   //加了这个，会回滚
	public void test() {
		List<Order> orders = new ArrayList<>();
		for (int i = 1; i <= 10; i++) {// test
			Order o = new Order();
			o.setLoanid("dd"+i);
			o.setUsername("aaaaaa"+i);
			orders.add(o);
		}
		System.out.println("list size ============" + (orders.size()));
		long start = System.currentTimeMillis();
		String updateSql = "UPDATE order_base a SET  a.account_id=?  WHERE loan_id =? ";
		jdbcTemplate.batchUpdate(updateSql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement preStatement, int i) throws SQLException {
				preStatement.setString(1, orders.get(i).getUsername());
				preStatement.setString(2, orders.get(i).getLoanid());
			}
			@Override
			public int getBatchSize() {
				return orders.size();
			}
		});
		System.out.println("============" + (System.currentTimeMillis() - start) / 1000);
	}

}